

homicides_2020 <- rbindlist(lapply(state.name, function(s){
  print(s)
  url <- paste0("https://www.countyhealthrankings.org/sites/default/files/media/document/2020%20County%20Health%20Rankings%20", gsub(" ", "%20", s), "%20Data%20-%20v1_0.xlsx")
  url2 <- paste0("https://www.countyhealthrankings.org/sites/default/files/media/document/2020%20County%20Health%20Rankings%20", gsub(" ", "%20", s), "%20Data%20-%20v1_1.xlsx")
  
  destfile <- "temp/file.xlsx"
  success <- F
  try({
    download.file(url, "temp/file.xlsx", mode = "wb")
    # Check if file was written and has size
    if (file.exists(destfile) && file.info(destfile)$size > 0) {
      message("Downloaded from URL 1 successfully.")
      success <- TRUE
    }
  }, silent = TRUE)
  # If the first download failed, try the second
  if (!success) {
    message("Trying fallback URL...")
    try({
      download.file(url2, destfile, mode = "wb")
      if (file.exists(destfile) && file.info(destfile)$size > 0) {
        message("Downloaded from URL 2 successfully.")
        success <- TRUE
      }
    }, silent = TRUE)
  }
  
  hm <- read_xlsx("temp/file.xlsx", sheet = "Additional Measure Data")
  file.remove("temp/file.xlsx")
  colnames(hm) <- hm[1,]
  
  hm <- select(hm, FIPS, homicide = `Homicide Rate`) |> 
    filter(row_number() != 1) |> 
    mutate(year = 2020)
  
  return(hm)
  
}))

saveRDS(homicides_2020, "temp/homicides_2020.rds")

homicides_2016 <- rbindlist(lapply(state.name, function(s){
  print(s)
  url1 <- paste0("https://www.countyhealthrankings.org/sites/default/files/media/document/state/downloads/2016%20County%20Health%20Rankings%20", gsub(" ", "%20", s), "%20Data%20-%20v1.xls")
  url2 <- paste0("https://www.countyhealthrankings.org/sites/default/files/media/document/state/downloads/2016%20County%20Health%20Rankings%20", gsub(" ", "%20", s), "%20Data%20-%20v2.xls")
  url3 <- paste0("https://www.countyhealthrankings.org/sites/default/files/media/document/state/downloads/2016%20County%20Health%20Rankings%20", gsub(" ", "%20", s), "%20Data%20-%20v3.xls")
  url4 <- paste0("https://www.countyhealthrankings.org/sites/default/files/media/document/state/downloads/2016%20County%20Health%20Rankings%20", gsub(" ", "%20", s), "%20Data%20-%20v4.xls")
  
  destfile <- "temp/file.xls"
  success <- F
  try({
    download.file(url1, "temp/file.xlsx", mode = "wb")
    # Check if file was written and has size
    if (file.exists(destfile) && file.info(destfile)$size > 0) {
      message("Downloaded from URL 1 successfully.")
      success <- TRUE
    }
  }, silent = TRUE)
  # If the first download failed, try the second
  if (!success) {
    message("Trying fallback URL...")
    try({
      download.file(url2, destfile, mode = "wb")
      if (file.exists(destfile) && file.info(destfile)$size > 0) {
        message("Downloaded from URL 2 successfully.")
        success <- TRUE
      }
    }, silent = TRUE)
  }
  if (!success) {
    message("Trying fallback URL...")
    try({
      download.file(url3, destfile, mode = "wb")
      if (file.exists(destfile) && file.info(destfile)$size > 0) {
        message("Downloaded from URL 3 successfully.")
        success <- TRUE
      }
    }, silent = TRUE)
  }
  if (!success) {
    message("Trying fallback URL...")
    try({
      download.file(url4, destfile, mode = "wb")
      if (file.exists(destfile) && file.info(destfile)$size > 0) {
        message("Downloaded from URL 4 successfully.")
        success <- TRUE
      }
    }, silent = TRUE)
  }
  
  hm <- read_xls(destfile, sheet = "Additional Measure Data")
  file.remove(destfile)
  colnames(hm) <- hm[1,]
  
  hm <- select(hm, FIPS, homicide = `Homicide Rate`) |> 
    filter(row_number() != 1) |> 
    mutate(year = 2016)
  
  return(hm)
  
}))

saveRDS(homicides_2016, "temp/homicides_2016.rds")
